package rak.r91.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import rak.r91.data.Item;
import rak.r91.data.ItemType;
import rak.r91.data.UnitType;

public class JdbcItemDao extends JdbcDaoSupport implements ItemDao{

	@Override
	public List<Item> getItems(int itemTypeID) {
		return getJdbcTemplate().query("SELECT item, name FROM item IT WHERE item_type_fk="+itemTypeID,new itemsMapper());
	}
	
	private static final class itemsMapper implements RowMapper<Item> {
	    public Item mapRow(ResultSet rs, int rowNum) throws SQLException {
	        Item item = new Item();
	        item.setItemID(rs.getInt("item"));
	        item.setName(rs.getString("name"));
	        return item;
	    }
	}

	@Override
	public int addItem(Item item) {
		getJdbcTemplate().update("INSERT INTO item(item_type_fk, name, sale_price, description, created)"+
									"VALUES ("+item.getItemType().getItemTypeID()+", '"+item.getName()+"', "+item.getSalePrice()+", '"+item.getDescription()+"','"+new java.sql.Timestamp(new java.util.Date().getTime())+"')");
		return lastInsertItemID();		
	}
	
	public int lastInsertItemID(){	
		return getJdbcTemplate().queryForInt("select max(item) from item");
	}

	@Override
	public Item getItem(int itemID) {		
		return getJdbcTemplate().queryForObject("SELECT I.item,I.name,I.store_price,I.sale_price,I.producer,I.description,I.producer_code,I.created,UT.unit_type,UT.type_name, UT.long_name,IT.item_type,IT.type_name  FROM item I LEFT JOIN unit_type UT ON I.unit_type_fk=UT.unit_type LEFT JOIN item_type IT ON I.item_type_fk=IT.item_type WHERE item = "+itemID, new itemMapper());
	}
	
	private static final class itemMapper implements RowMapper<Item> {
	    public Item mapRow(ResultSet rs, int rowNum) throws SQLException {
	        Item item = new Item();
	        item.setItemID(rs.getInt("item"));
	        item.setName(rs.getString("name"));
	        item.setStorePrice(rs.getDouble("store_price"));
	        item.setSalePrice(rs.getDouble("sale_price"));
	        item.setProducer(rs.getString("producer"));
	        item.setDescription(rs.getString("description"));
	        item.setProducerCode(rs.getString("producer_code"));
	        item.setCreated(rs.getTimestamp("created"));
	        UnitType unitType = new UnitType();
	        unitType.setUnitTypeID(rs.getInt("unit_Type"));
	        unitType.setTypeName(rs.getString("type_name"));
	        unitType.setLongName(rs.getString("long_name"));
	        ItemType itemType = new ItemType();
	        itemType.setItemTypeID(rs.getInt("item_type"));
	        itemType.setItemType(rs.getString("type_name"));	        
	        item.setItemType(itemType);
	        item.setUnitType(unitType);
	        return item;
	    }
	}

	@Override
	public int updateItem(Item item) {
		return getJdbcTemplate().update("UPDATE item SET name = ?, sale_price = ?, description = ? WHERE item = ?", new Object[]{item.getName(),item.getSalePrice(),item.getDescription(),item.getItemID()});
	}
	
	
	
//	getJdbcTemplate().update("INSERT INTO item(unit_type_fk, supplier_enteprise_fk, item_type_fk, name, sale_price, producer, description, producer_code, created)"+
//			"VALUES ("+item.getUnitType().getUnitTypeID()+", "+item.getSupplierEnteprise()+", "+item.getItemType()+", "+item.getName()+", "+item.getSalePrice()+", "+item.getProducer()+")");

}
